A Relational DataBase Management System (RDBMS) uses relational techniques for storing and retrieving data in a relational database. Relational databases are computerized information storage and retrieval systems. Relational databases are organized into tables that consist of rows and columns of data. The rows may be called tuples or records or rows.
A table in a database may be accessed using an index. An index is an ordered set of references (e.g., pointers) to the records in the table. The index is used to access each record in the table using a key (i.e., one of the fields or attributes of the record, which corresponds to a column). The term “key” may also be referred to as “index key”. Without an index, finding a record requires a scan (e.g., linearly) of an entire table.
The RDBMS may accept queries, such as Structured Query Language (SQL) queries and non-SQL queries (e.g., JavaScript® Object Notation (JSON) queries). JavaScript is a registered trademark of Oracle Corporation in the United States and/or other countries. A query may be described as a request for information from a database based on specific conditions. A query typically includes one or more predicates. A predicate may be described as an element of a search condition that expresses or implies a comparison operation (e.g., A=3).
When customers create a simple table (without partitioning), they specify a tablespace (e.g., a dbspace) or use a default tablespace. Partitioning may be described as dividing the table into multiple partitions, so that different portions of the table are stored in different partitions. Customers typically do not worry about underlying storage allocation decisions on chunks, extents, and pages that store the table.
In the real world, for time cyclic data management and better performance, customers may create partitioned tables. Time cyclic data management may be described as a system in which there is data for different time periods. If customers choose to partition the table based on a range of values (i.e., range partitioning), there are many decisions to be made, such as, the periodic downtime required to adjust the range (partition expression) definition, the physical schema definition, and creating CRON jobs to execute commands to implement a table policy. A CRON utility may be described as a time-based job scheduler in UNIX® or LINUX® operating systems. UNIX is a registered trademark of The Open Group in the United States and/or other countries. LINUX is a registered trademark of Linus Torvalds in the United States and/or other countries.
For time cyclic data management, data for each time period (hour, day, week, month, quarter, year, etc.) may be kept in distinct partitions to improve the manageability of data. The data is rolled in (e.g., to a new partition) and rolled out (e.g., from an existing partition) in regular intervals. Corresponding effects on indices are typically managed implicitly by the database system. How the rolled out data is managed has to be decided (e.g., decide on whether to keep the data around, archive the data or remove the data). In some cases, the data is not being rolled out, but the data is being moved from one partition to other (probably slower/cheaper) media. During this process, the data may be compressed. In some cases (e.g., the data has been archived or is no longer needed), the partition may be truncated and the space for that partition may be re-used. Also, there are other operations, such as updating the statistics to reflect the remaining data (e.g., if the partition was rolled out or truncated) . Rolled out refers to detaching a partition (and therefore data in that partition) from a table. Truncate refers to leaving the partition in the table, while removing the data by freeing up the space allocated for that data.
Customers may write a series of SQL scripts for operations, such as attaching a partition and detaching a partition, schedule downtime, and run the scripts at regular intervals manually or using CRON jobs.
One of the common operations on the table is to detach (either detach+archive or detach+purge) the oldest partition when rolling into a new time period.
The following is an example SQL Statement (A) for partition by expression:
CREATE TABLE orders ( order_num    serial(1001), order_date   date, customer_num    integer not null, ship_instruct   char(40), backlog char(1), po_num   char(10), ship_date  date, ship_weight   decimal(8,2), ship_charge   money(6), paid date  date  ) partition by expressionpartition prv_partition (order_date < date(’01-01-2010’)) in mydbs,partition jan_partition (order_date >= date(’01-01-2010’) and order_date <date(’02-01-2010’) in mydbs,partition feb_partition (order_date >= date(’02-01-2010’) and order_date <date(’03-01-2010’) in mydbs,partition mar_partition (order_date >= date(’03-01-2010’) and order_date <date(’04-01-2010’) in mydbs,partition apr_partition (order_date >= date(’04-01-2010’) and order_date <date(’05-01-2010’) in mydbs,
The following is an example SQL Statement (B) for partition by interval:
CREATE TABLE orders  ( order_num    serial(1001), order_date   date, customer_num     integer not null, ship_instruct   char(40), backlog char(1), po_num   char(10), ship_date date, ship_weight  decimal(8,2), ship_charge  money(6), paid_date date  )partition by range(order_date) interval((NUMTOYMINTERVAL(1,′MONTH′)) store in (mydbs, mydbs2)partition prv_partition values < date(’01-01-2010’) in mydbs;
The following is an example script with a DETACH PARTITION clause:
ALTER PARTITION ON TABLE “sales”.orders DETACH PARTITION jan_partition jan_2010_orders;
OFFLINE refers to an operation taking an exclusive lock on the table, and no other user may use that locked table. ONLINE refers to having concurrent users access a table.
The following is an example script with an ONLINE operation with an INTERVAL strategy:
ALTER PARTITION ONLINE ON TABLE “sales”.orders DETACH PARTITION jan_partition jan_2010_orders;
Continuing with the example, script, the data that has been detached is handled by compressing the data or dropping the table. A detached partition is created as a separate table having a table name same that is the same as the partition name (e.g., jan_2010_orders).
-- Compress the dataexecute procedure task(“compress repack shrink”, “salesdb”, “sales”,“jan_2010_orders”);-- or Drop the tabledrop table “sales”.jan_2010_orders;
Some operations, such as dropping a partition, may require an exclusive lock and access to the table from which a partition is being detached. During the exclusive lock, queries on that locked table have to wait or may time out.